<?php

Yii::import('application.models._base.BaseChartMaster');
class ChartMaster extends BaseChartMaster
{
    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }
    public static function get_saldo_periode($from, $to, $account)
    {
        $comm = Yii::app()->db->createCommand("SELECT
        Sum(pgt.amount) FROM psn_gl_trans AS pgt
        WHERE pgt.tran_date >= :from AND
        pgt.tran_date <= :to AND
        pgt.account_code = :account_code");
        return $comm->queryScalar(array(':from' => $from, ':to' => $to, ':account_code' => $account));
    }
    public static function get_saldo_periode_kategori($from, $to, $account)
    {
        $comm = Yii::app()->db->createCommand("SELECT pcm.account_code,pcm.account_name,
        IFNULL(Sum(pgt.amount),0) total FROM psn_gl_trans AS pgt
	    RIGHT JOIN psn_chart_master pcm ON (pgt.account_code = pcm.account_code AND
        pgt.tran_date >= :from AND pgt.tran_date <= :to)
        WHERE pcm.kategori = :account_code GROUP BY pcm.account_code");
        return $comm->queryScalar(array(':from' => $from, ':to' => $to,
            ':account_code' => $account));
    }
    public static function get_laba_rugi($from,$to)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT lb.account_code,lb.account_name,IFNULL(Sum(pgt.amount),0) total FROM
        (SELECT pcm.account_code,pcm.account_name
        FROM psn_chart_master pcm
        WHERE pcm.kategori IN (:pendapatan,:hpp,:bebanadm,:bebanpemasaran,:pendapatanlain,:bebanlain)
        OR pcm.account_code = :pjkpenghasilan) lb
        LEFT JOIN psn_chart_master pcm1 ON(lb.account_code = pcm1.kategori)
        LEFT JOIN psn_chart_master pcm2 ON(pcm1.account_code = pcm2.kategori)
        LEFT JOIN psn_chart_master pcm3 ON(pcm2.account_code = pcm3.kategori)
        LEFT JOIN psn_chart_master pcm4 ON(pcm3.account_code = pcm4.kategori)
        LEFT JOIN psn_gl_trans AS pgt ON(COALESCE(pcm4.account_code,pcm3.account_code,
        pcm2.account_code,pcm1.account_code,lb.account_code) = pgt.account_code AND
        pgt.tran_date >= :from AND pgt.tran_date <= :to)
        GROUP BY lb.account_code");
        return $comm->queryAll(true, array(':from' => $from, ':to' => $to,
            ':pendapatan' => KATEGORIPENDAPATAN, ':hpp' => KATEGORIHPP,
            ':bebanadm' => KATEGORIBEBANADMINISTRASI, ':bebanpemasaran' => KATEGORIBEBANPEMASARAN,
            ':pendapatanlain' => KATEGORIPENDAPATANLAIN, ':bebanlain' => KATEGORIBEBANLAIN,
            ':pjkpenghasilan' => PAJAKPENGHASILAN
        ));
    }
    public static function get_neraca($from,$to){
        $comm = Yii::app()->db->createCommand("
        SELECT lb.account_code,lb.account_name,IFNULL(Sum(pgt.amount),0) total FROM
        (SELECT pcm.account_code,pcm.account_name
        FROM psn_chart_master pcm
        WHERE pcm.kategori IN ('11100','11200','11300','21000','22000','30000')
				OR pcm.account_code REGEXP '^12[1-9]') lb
        LEFT JOIN psn_chart_master pcm1 ON(lb.account_code = pcm1.kategori)
        LEFT JOIN psn_chart_master pcm2 ON(pcm1.account_code = pcm2.kategori)
        LEFT JOIN psn_chart_master pcm3 ON(pcm2.account_code = pcm3.kategori)
        LEFT JOIN psn_chart_master pcm4 ON(pcm3.account_code = pcm4.kategori)
        LEFT JOIN psn_gl_trans AS pgt ON(COALESCE(pcm4.account_code,pcm3.account_code,
        pcm2.account_code,pcm1.account_code,lb.account_code) = pgt.account_code AND
        pgt.tran_date >= :from AND pgt.tran_date <= :to)
        GROUP BY lb.account_code");
        return $comm->queryAll(true,array(':from' => $from, ':to' => $to));
    }
    public static function get_saldo_until($tgl, $account)
    {
        $comm = Yii::app()->db->createCommand("SELECT
        Sum(pgt.amount) FROM psn_gl_trans AS pgt
        WHERE pgt.tran_date <= :tgl AND
        pgt.account_code = :account_code");
        return $comm->queryScalar(array(':tgl' => $tgl, ':account_code' => $account));
    }
}